﻿using Kjs.Report.DB;
using Kjs.Report.IService;
using Kjs.Report.Model;
using System;
using System.Collections.Generic;
using System.Text;

namespace Kjs.Report.Service
{
    /// <summary>
    /// 订单报表
    /// </summary>
    public class OrderService : IOrderService
    {

        /// <summary>
        /// 按照设备分组统计销售明细
        /// </summary>
        /// <param name="pageSize">每页数量</param>
        /// <param name="pageIndex">当前页码</param>
        /// <param name="filedOrder">排序字段</param>
        /// <param name="orgIds">组织编号[多个用  , 隔开]</param>
        /// <param name="line_id">线路编号</param>
        /// <param name="point_id">点位编号</param>
        /// <param name="beginTime">开始时间</param>
        /// <param name="endTime">结束时间</param>
        /// <returns></returns>
        public PagedList<IList<M_TerminalGroup>> TerminalGroupList(int pageSize, int pageIndex, string filedOrder, string orgIds, int line_id, int point_id,
            DateTime? beginTime, DateTime? endTime)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"SELECT
	a.terminal_no,
	e.`name`,
	e.address,
	count(b.quantity) total_count,
	sum(
		a.real_amount - ifnull(a.refund_amount, 0)
	) total_amount
FROM
	orders a
JOIN order_good b ON a.id = b.order_id
JOIN terminals e ON a.terminal_id = e.id
WHERE
	a.`status` = 3
");
            if (!string.IsNullOrWhiteSpace(orgIds))
                strSql.AppendFormat(" and a.org_id in ({0}) ", orgIds);

            if (line_id > 0)
                strSql.AppendFormat(" and e.line_id = {0} ", line_id);

            if (point_id > 0)
                strSql.AppendFormat(" and e.point_id = {0} ", point_id);

            if (beginTime != null)
                strSql.AppendFormat(" and a.add_time >= '{0}' ", beginTime);

            if (endTime != null)
                strSql.AppendFormat(" and a.add_time < '{0}' ", endTime);

            strSql.Append(" group by a.terminal_no,e.`name`,e.address  ");

            if (string.IsNullOrWhiteSpace(filedOrder))
                filedOrder = " e.`name` desc ";

            return PagedHelper.PagedList<M_TerminalGroup>(strSql.ToString(), pageSize, pageIndex, filedOrder);
        }

        /// <summary>
        /// 按照商品分组统计销售明细
        /// </summary>
        /// <param name="orgIds">组织编号[多个用  , 隔开]</param>
        /// <param name="line_id">线路编号</param>
        /// <param name="point_id">点位编号</param>
        /// <param name="beginTime">开始时间</param>
        /// <param name="endTime">结束时间</param>
        /// <returns></returns>
        public PagedList<IList<M_GoodTitleGroup>> GoodTitleGroupList(int pageSize, int pageIndex, string filedOrder, string orgIds, int line_id, int point_id,
            DateTime? beginTime, DateTime? endTime)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"SELECT
	b.good_title,
	count(b.quantity) total_count,
	sum(
		a.real_amount - ifnull(a.refund_amount, 0)
	) total_amount
FROM
	orders a
JOIN order_good b ON a.id = b.order_id 
JOIN terminals e ON a.terminal_id = e.id
WHERE
	a.`status` = 3
");
            if (!string.IsNullOrWhiteSpace(orgIds))
                strSql.AppendFormat(" and a.org_id in ({0}) ", orgIds);

            if (line_id > 0)
                strSql.AppendFormat(" and e.line_id = {0} ", line_id);

            if (point_id > 0)
                strSql.AppendFormat(" and e.point_id = {0} ", point_id);

            if (beginTime != null)
                strSql.AppendFormat(" and a.add_time >= '{0}' ", beginTime);

            if (endTime != null)
                strSql.AppendFormat(" and a.add_time < '{0}' ", endTime);

            strSql.Append(" group by b.good_title  ");

            if (string.IsNullOrWhiteSpace(filedOrder))
                filedOrder = " total_amount desc ";

            return PagedHelper.PagedList<M_GoodTitleGroup>(strSql.ToString(), pageSize, pageIndex, filedOrder);

        }

        /// <summary>
        ///  按照商品分类分组统计销售明细
        /// </summary>
        /// <param name="orgIds">组织编号[多个用  , 隔开]</param>
        /// <param name="line_id">线路编号</param>
        /// <param name="point_id">点位编号</param>
        /// <param name="beginTime">开始时间</param>
        /// <param name="endTime">结束时间</param>
        /// <returns></returns>
        public PagedList<IList<M_CategoryGroup>> CategoryGroupList(int pageSize, int pageIndex, string filedOrder, string orgIds, int line_id, int point_id,
            DateTime? beginTime, DateTime? endTime)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"SELECT
	g.title,
	sum(sCount) total_count,
	sum(tAmount) total_amount
FROM
	(");
            strSql.Append(@"
			SELECT
			b.good_id,
			count(b.quantity) sCount,
			sum(
				a.real_amount - ifnull(a.refund_amount, 0)
			) tAmount
		FROM
			orders a
		JOIN order_good b ON a.id = b.order_id
		JOIN terminals e ON a.terminal_id = e.id
 WHERE
	a.`status` = 3
");
            if (!string.IsNullOrWhiteSpace(orgIds))
                strSql.AppendFormat(" and a.org_id in ({0}) ", orgIds);

            if (line_id > 0)
                strSql.AppendFormat(" and e.line_id = {0} ", line_id);

            if (point_id > 0)
                strSql.AppendFormat(" and e.point_id = {0} ", point_id);

            if (beginTime != null)
                strSql.AppendFormat(" and a.add_time >= '{0}' ", beginTime);

            if (endTime != null)
                strSql.AppendFormat(" and a.add_time < '{0}' ", endTime);

            strSql.Append(" group by b.good_id  ");
            strSql.Append(@"
    ) t
    JOIN goods f ON t.good_id = f.id
    JOIN good_category g ON f.category_id = g.id
    GROUP BY
	    g.title
");

            if (string.IsNullOrWhiteSpace(filedOrder))
                filedOrder = " total_amount desc ";

            return PagedHelper.PagedList<M_CategoryGroup>(strSql.ToString(), pageSize, pageIndex, filedOrder);
        }

        /// <summary>
        ///  按照日期分组统计销售明细
        /// </summary>
        /// <param name="orgIds">组织编号[多个用  , 隔开]</param>
        /// <param name="line_id">线路编号</param>
        /// <param name="point_id">点位编号</param>
        /// <param name="beginTime">开始时间</param>
        /// <param name="endTime">结束时间</param>
        /// <returns></returns>
        public PagedList<IList<M_TimeGroup>> TimeGroupList(int pageSize, int pageIndex, string filedOrder, string orgIds, int line_id, int point_id,
            DateTime? beginTime, DateTime? endTime)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"SELECT
	DATE_FORMAT(a.add_time, '%Y-%m-%d') time,
	count(b.quantity) total_count,
	sum(
		a.real_amount - ifnull(a.refund_amount, 0)
	) total_amount
FROM
	orders a
JOIN order_good b ON a.id = b.order_id
JOIN terminals e ON a.terminal_id = e.id
WHERE
	a.`status` = 3
");
            if (!string.IsNullOrWhiteSpace(orgIds))
                strSql.AppendFormat(" and a.org_id in ({0}) ", orgIds);

            if (line_id > 0)
                strSql.AppendFormat(" and e.line_id = {0} ", line_id);

            if (point_id > 0)
                strSql.AppendFormat(" and e.point_id = {0} ", point_id);

            if (beginTime != null)
                strSql.AppendFormat(" and a.add_time >= '{0}' ", beginTime);

            if (endTime != null)
                strSql.AppendFormat(" and a.add_time < '{0}' ", endTime);

            strSql.Append(" group by time  ");

            if (string.IsNullOrWhiteSpace(filedOrder))
                filedOrder = " time desc ";

            return PagedHelper.PagedList<M_TimeGroup>(strSql.ToString(), pageSize, pageIndex, filedOrder);
        }


        /// <summary>
        ///  按照日期分组统计销售明细
        /// </summary>
        /// <param name="orgIds">组织编号[多个用  , 隔开]</param>
        /// <param name="line_id">线路编号</param>
        /// <param name="point_id">点位编号</param>
        /// <param name="beginTime">开始时间</param>
        /// <param name="endTime">结束时间</param>
        /// <returns></returns>
        public PagedList<IList<M_TimeStatusGroup>> TimeStatusGroupList(int pageSize, int pageIndex, string filedOrder, string orgIds, int line_id, int point_id,
            DateTime? beginTime, DateTime? endTime)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"SELECT
	t.time,
	MAX(CASE WHEN t.`status`=1 then t.count ELSE 0 end) as status_1,
	MAX(CASE WHEN t.`status`=2 then t.count ELSE 0 end) as status_2,
	MAX(CASE WHEN t.`status`=3 then t.count ELSE 0 end) as status_3,
	MAX(CASE WHEN t.`status`=4 then t.count ELSE 0 end) as status_4,
	MAX(CASE WHEN t.`status`=5 then t.count ELSE 0 end) as status_5,
	MAX(CASE WHEN t.`status`=6 then t.count ELSE 0 end) as status_6,
	MAX(CASE WHEN t.`status`=7 then t.count ELSE 0 end) as status_7,
	MAX(CASE WHEN t.`status`=8 then t.count ELSE 0 end) as status_8,
	SUM(t.count) count
FROM
	(
		SELECT
			t1.time,
			t1.`status`,
			SUM(t1.count) count
		FROM
			(
				SELECT
					DATE_FORMAT(a.add_time, '%Y-%m-%d') time,
					a.terminal_id,
					a.`status`,
					count(1) count
				FROM
					orders a
				LEFT JOIN terminals e ON a.terminal_id = e.id
				WHERE
        1 = 1
");
            if (!string.IsNullOrWhiteSpace(orgIds))
                strSql.AppendFormat(" and a.org_id in ({0}) ", orgIds);

            if (beginTime != null)
                strSql.AppendFormat(" and a.add_time >= '{0}' ", beginTime);

            if (endTime != null)
                strSql.AppendFormat(" and a.add_time < '{0}' ", endTime);


            if (line_id > 0)
                strSql.AppendFormat(" and e.line_id = {0} ", line_id);

            if (point_id > 0)
                strSql.AppendFormat(" and e.point_id = {0} ", point_id);


            strSql.Append(@" 	GROUP BY
					time,
					a.terminal_id,
					a.`status`
			) t1
		GROUP BY
			t1.time,
			t1.`status`
	) t
GROUP BY
	t.time
");
            if (string.IsNullOrWhiteSpace(filedOrder))
                filedOrder = " time desc ";

            return PagedHelper.PagedList<M_TimeStatusGroup>(strSql.ToString(), pageSize, pageIndex, filedOrder);
        }
        //TODO 订单表设备编号存在为0情况，需查明原因

        /// <summary>
        ///  按照日期分组统计销售明细
        /// </summary>
        /// <param name="orgIds">组织编号[多个用  , 隔开]</param>
        /// <param name="line_id">线路编号</param>
        /// <param name="point_id">点位编号</param>
        /// <param name="beginTime">开始时间</param>
        /// <param name="endTime">结束时间</param>
        /// <returns></returns>
        public PagedList<IList<M_TerminalStatusGroup>> TerminalStatusGroupList(int pageSize, int pageIndex, string filedOrder, string orgIds, int line_id, int point_id,
            DateTime? beginTime, DateTime? endTime)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"SELECT
	e.`name`,
	e.terminal_no,
	e.address,	
  MAX(CASE WHEN t.`status`=1 then t.count ELSE 0 end) as status_1,
	MAX(CASE WHEN t.`status`=2 then t.count ELSE 0 end) as status_2,
	MAX(CASE WHEN t.`status`=3 then t.count ELSE 0 end) as status_3,
	MAX(CASE WHEN t.`status`=4 then t.count ELSE 0 end) as status_4,
	MAX(CASE WHEN t.`status`=5 then t.count ELSE 0 end) as status_5,
	MAX(CASE WHEN t.`status`=6 then t.count ELSE 0 end) as status_6,
	MAX(CASE WHEN t.`status`=7 then t.count ELSE 0 end) as status_7,
	MAX(CASE WHEN t.`status`=8 then t.count ELSE 0 end) as status_8,
	SUM(t.count) count
FROM
	(
		SELECT
			a.terminal_id,
			a.`status`,
			count(1) count
		FROM
			orders a
		WHERE
        1 = 1
");
            if (!string.IsNullOrWhiteSpace(orgIds))
                strSql.AppendFormat(" and a.org_id in ({0}) ", orgIds);
            if (beginTime != null)
                strSql.AppendFormat(" and a.add_time >= '{0}' ", beginTime);

            if (endTime != null)
                strSql.AppendFormat(" and a.add_time < '{0}' ", endTime);
            strSql.Append(@"	GROUP BY
			a.terminal_id,
			a.`status`
	) t
LEFT JOIN terminals e ON t.terminal_id = e.id
WHERE
	1 = 1");

            if (line_id > 0)
                strSql.AppendFormat(" and e.line_id = {0} ", line_id);

            if (point_id > 0)
                strSql.AppendFormat(" and e.point_id = {0} ", point_id);



            strSql.Append(@" GROUP BY
	e.`name`,
	e.terminal_no,
    e.address
");
            if (string.IsNullOrWhiteSpace(filedOrder))
                filedOrder = " status_3 desc ";

            return PagedHelper.PagedList<M_TerminalStatusGroup>(strSql.ToString(), pageSize, pageIndex, filedOrder);
        }
    }
}
